﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace POC
{
    static class AddressEntryDAO
    {
        public static List<AddressEntry> LoadAll()
        {
            string connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\AddressBook.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

            SqlConnection con = new SqlConnection(connectionString);

            SqlCommand command = new SqlCommand("Select * From AddressEntry", con);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;

            DataTable table = new DataTable();
            table.Locale = System.Globalization.CultureInfo.InvariantCulture;
            adapter.Fill(table);

            List<AddressEntry> list = new List<AddressEntry>();
            foreach (DataRow dr in table.Rows)
            {
                AddressEntry add = new AddressEntry(dr["FirstName"].ToString(),dr["LastName"].ToString(), dr["Email"].ToString());
                add.Key = (int)dr["Key"];
                list.Add(add);
            }
            if (table.Rows.Count > 0)
            {
                Globals.lastClicked = new AddressEntry(table.Rows[0]["FirstName"].ToString(),table.Rows[0]["LastName"].ToString(), table.Rows[0]["Email"].ToString());
            }
            else
            {
                Globals.lastClicked = new AddressEntry("", "","");
            }
            return list;
        }
        public static void Update(AddressEntry entry)
        {
            string connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\AddressBook.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            SqlCommand command = con.CreateCommand();
            command.CommandText = "UPDATE AddressEntry SET FirstName=@fname, LastName=@lname, Email=@email WHERE FirstName=@oldfName and LastName=@oldlname and Email=@oldEmail";
            command.Parameters.Add("@fname", SqlDbType.NVarChar, 50);
            command.Parameters.Add("@lname", SqlDbType.NVarChar, 50);
            command.Parameters.Add("@email", SqlDbType.NVarChar, 50);
            command.Parameters.Add("@oldfName", SqlDbType.NVarChar, 50);
            command.Parameters.Add("@oldlName", SqlDbType.NVarChar, 50);
            command.Parameters.Add("@oldEmail", SqlDbType.NVarChar, 50);
            command.Prepare();
            command.Parameters["@fname"].Value = entry.FirstName;
            command.Parameters["@lname"].Value = entry.LastName;
            command.Parameters["@email"].Value = entry.Email;
            command.Parameters["@oldfName"].Value = Globals.lastClicked.FirstName;
            command.Parameters["@oldlName"].Value = Globals.lastClicked.LastName;
            command.Parameters["@oldEmail"].Value = Globals.lastClicked.Email;

            Globals.lastClicked.FirstName = entry.FirstName;
            Globals.lastClicked.LastName = entry.LastName;
            Globals.lastClicked.Email = entry.Email;
            int rows = command.ExecuteNonQuery();
            con.Close();
            
        }
        public static void Create(AddressEntry entry)
        {
            string connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\AddressBook.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            SqlCommand command = con.CreateCommand();
            command.CommandText = "INSERT INTO AddressEntry (FirstName, LastName, Email) VALUES (@fname, @lname, @email)";
            command.Parameters.Add("@fname", SqlDbType.NVarChar, 50);
            command.Parameters.Add("@lname", SqlDbType.NVarChar, 50);
            command.Parameters.Add("@email", SqlDbType.NVarChar, 50);
            command.Prepare();
            command.Parameters["@fname"].Value = entry.FirstName;
            command.Parameters["@lname"].Value = entry.LastName;
            command.Parameters["@email"].Value = entry.Email;
            
            Globals.lastClicked.FirstName = entry.FirstName;
            Globals.lastClicked.LastName = entry.LastName;
            Globals.lastClicked.Email = entry.Email;
            int rows = command.ExecuteNonQuery();
            con.Close();
        }
        public static void Delete(AddressEntry entry)
        {
            string connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\AddressBook.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            SqlCommand command = con.CreateCommand();
            command.CommandText = "DELETE FROM AddressEntry WHERE FirstName=@fname and LastName=@lname and Email=@email";
            command.Parameters.Add("@fname", SqlDbType.NVarChar, 50);
            command.Parameters.Add("@lname", SqlDbType.NVarChar, 50);
            command.Parameters.Add("@email", SqlDbType.NVarChar, 50);
            command.Prepare();
            command.Parameters["@fname"].Value = entry.FirstName;
            command.Parameters["@lname"].Value = entry.LastName;
            command.Parameters["@email"].Value = entry.Email;

            int rows = command.ExecuteNonQuery();
            con.Close();
        }
    }
}
